package com.FH.applyFrom.salary_bill;

import com.FH.organization.Attachment.AttachmentPullDTO;
import com.FH.organization.people.KamMemberResource;
import com.FH.organization.remind.RemindPullDTO;
import com.FH.organization.remind.RemindResource;
import com.FH.permission.member_role.MemberRoleResource;
import com.FH.util.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.codehaus.jettison.json.JSONArray;
import org.codehaus.jettison.json.JSONException;
import org.codehaus.jettison.json.JSONObject;

import javax.ejb.Stateless;
import javax.inject.Inject;
import javax.ws.rs.core.Response;
import java.io.File;
import java.io.FileOutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by Administrator on 2017/8/14 0014.
 */
@Stateless
public class SalaryBillDAOImpl extends AccessDB implements SalaryBillDAO{

    @Inject
    MemberRoleResource memberRoleResource;

    @Inject
    KamMemberResource kamMemberResource;

    @Inject
    RemindResource remindResource;

    /**
     * 工资条信息查询
     *
     * @param search
     * @param page
     * @param size
     */
    @Override
    public ResultDTO getSalaryBillInfo(String search, String page, String size) {

        ResultDTO resultDTO = new ResultDTO();

        try {
            JSONObject json = new JSONObject(search);

            String sqlexit = "select count(1) from salary_bill where 1=1";

            String sql_w = "";//查询sql组合
            String sql_page;//分页sql组合
            Integer page_ok = Integer.parseInt(page);
            Integer size_ok = Integer.parseInt(size);
            if(page.equals("1")){
                sql_page = " limit 0," + size;
            }else {
                sql_page = " limit " + ((page_ok-1)*size_ok) + "," + size;
            }
            if (json.has("salary_bill_id")){
                if(null != json.getString("salary_bill_id") && !json.getString("salary_bill_id").toString().isEmpty()){
                    sql_w += " and salary_bill_id = '"+json.getString("salary_bill_id")+"'";
                }

            }
            if (json.has("month")){
                if(null != json.getString("month") && !json.getString("month").toString().isEmpty()){
                    sql_w += " and month = '"+json.getString("month")+"'";
                }

            }
            if (json.has("member_id")){
                if(null != json.getString("member_id") && !json.getString("member_id").toString().isEmpty()){
                    sql_w += " and member_id = '"+json.getString("member_id")+"'";
                }
            }
            if (json.has("name")){
                if(null != json.getString("name") && !json.getString("name").toString().isEmpty()){
                    sql_w += " and member_name = '"+json.getString("member_name")+"'";
                }
            }
            if (json.has("base_wage")){
                if(null != json.getString("base_wage") && !json.getString("base_wage").toString().isEmpty()){
                    sql_w += " and base_wage = '"+json.getString("base_wage")+"'";
                }

            }
            String sql_ok = sqlexit+sql_w;
            String existName = getReportingToOne(sql_ok);

            List<String[]> seresult = new ArrayList<String[]>();
            List<SalaryBillShowDTO> result = new ArrayList<SalaryBillShowDTO>();

            if(!existName.equals("0")){
                String sql = "select salary_bill_id,month,member_id,member_name,base_wage,position_salary,merit_pay,school_age_wage,allowance," +
                        "post_subsidy,meal_allowance,overtime_allowance,holiday_overtime_allowance,salary_add_total,compassionate_leave,sick_leave," +
                        "join_or_quit,probation_period,endowment_insurance,housing_fund,salary_deduct_total,other_payroll_credit,salary_total," +
                        "income_tax,actual_salary,remark,create_time,update_time,other_one,other_two,other_three,other_four,other_five " +
                        "from salary_bill where 1=1" + sql_w + sql_page;
                seresult = this.getReportingTo(sql);
//                System.out.println(sql);
                for (int i=0;i<seresult.size();i++){
                    SalaryBillShowDTO salaryBillShowDTO = new SalaryBillShowDTO();
                    salaryBillShowDTO.setSalary_bill_id(seresult.get(i)[1] == null ? "" : seresult.get(i)[1]);
                    salaryBillShowDTO.setMonth(seresult.get(i)[2]  == null ? "" : seresult.get(i)[2]);
                    salaryBillShowDTO.setMember_id(seresult.get(i)[3] == null ? "" : seresult.get(i)[3]);
                    salaryBillShowDTO.setMember_name(seresult.get(i)[4] == null ? "" : seresult.get(i)[4]);
                    salaryBillShowDTO.setBase_wage(seresult.get(i)[5] == null ? "" : seresult.get(i)[5]);
                    salaryBillShowDTO.setPosition_salary(seresult.get(i)[6] == null ? "" : seresult.get(i)[6]);
                    salaryBillShowDTO.setMerit_pay(seresult.get(i)[7] == null ? "" : seresult.get(i)[7]);
                    salaryBillShowDTO.setSchool_age_wage(seresult.get(i)[8] == null ? "" : seresult.get(i)[8]);
                    salaryBillShowDTO.setAllowance(seresult.get(i)[9] == null ? "" : seresult.get(i)[9]);
                    salaryBillShowDTO.setPost_subsidy(seresult.get(i)[10] == null ? "" : seresult.get(i)[10]);
                    salaryBillShowDTO.setMeal_allowance(seresult.get(i)[11] == null ? "" : seresult.get(i)[11]);
                    salaryBillShowDTO.setOvertime_allowance(seresult.get(i)[12] == null ? "" : seresult.get(i)[12]);

                    salaryBillShowDTO.setHoliday_overtime_allowance(seresult.get(i)[13] == null ? "" : seresult.get(i)[13]);

                    salaryBillShowDTO.setSalary_add_total(seresult.get(i)[14] == null ? "" : seresult.get(i)[14]);
                    salaryBillShowDTO.setCompassionate_leave(seresult.get(i)[15] == null ? "" : seresult.get(i)[15]);
                    salaryBillShowDTO.setSick_leave(seresult.get(i)[16] == null ? "" : seresult.get(i)[16]);
                    salaryBillShowDTO.setJoin_or_quit(seresult.get(i)[17] == null ? "" : seresult.get(i)[17]);
                    salaryBillShowDTO.setProbation_period(seresult.get(i)[18] == null ? "" : seresult.get(i)[18]);
                    salaryBillShowDTO.setEndowment_insurance(seresult.get(i)[19] == null ? "" : seresult.get(i)[19]);
                    salaryBillShowDTO.setHousing_fund(seresult.get(i)[20] == null ? "" : seresult.get(i)[20]);
                    salaryBillShowDTO.setSalary_deduct_total(seresult.get(i)[21] == null ? "" : seresult.get(i)[21]);
                    salaryBillShowDTO.setOther_payroll_credit(seresult.get(i)[22] == null ? "" : seresult.get(i)[22]);
                    salaryBillShowDTO.setSalary_total(seresult.get(i)[23] == null ? "" : seresult.get(i)[23]);
                    salaryBillShowDTO.setIncome_tax(seresult.get(i)[24] == null ? "" : seresult.get(i)[24]);
                    salaryBillShowDTO.setActual_salary(seresult.get(i)[25] == null ? "" : seresult.get(i)[25]);
                    salaryBillShowDTO.setRemark(seresult.get(i)[26] == null ? "" : seresult.get(i)[26]);
                    salaryBillShowDTO.setCreate_time(seresult.get(i)[27] == null ? "" : seresult.get(i)[27]);
                    salaryBillShowDTO.setUpdate_time(seresult.get(i)[28] == null ? "" : seresult.get(i)[28]);

                    salaryBillShowDTO.setOther_one(seresult.get(i)[29] == null ? "" : seresult.get(i)[29]);
                    salaryBillShowDTO.setOther_two(seresult.get(i)[30] == null ? "" : seresult.get(i)[30]);
                    salaryBillShowDTO.setOther_three(seresult.get(i)[31] == null ? "" : seresult.get(i)[31]);
                    salaryBillShowDTO.setOther_four(seresult.get(i)[32] == null ? "" : seresult.get(i)[32]);
                    salaryBillShowDTO.setOther_five(seresult.get(i)[33] == null ? "" : seresult.get(i)[33]);

                    ResultDTO member=null;
                    /**
                     * 人员信息
                     */
                    if(seresult.get(i)[3] != null && !seresult.get(i)[3].equals("")){
                        member = memberRoleResource.getOfficeInfo("{member_id:" + seresult.get(i)[3] + "}", "1", "20");
                        salaryBillShowDTO.setMemberRole(member.getValues());
                    }

                    result.add(salaryBillShowDTO);
                }
                if (result.size() != 0) {
                    resultDTO.setStatus("90000");
                    resultDTO.setDescritpion(warnSrc.get_success);
                } else {
                    resultDTO.setStatus("90001");
                    resultDTO.setDescritpion(warnSrc.get_fail);
                }
                resultDTO.setStr(result.size() + "");
                resultDTO.setValues(result);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return resultDTO;
    }

    /**
     * 工资条信息添加
     *
     * @param salaryBillPullDTO
     */
    @Override
    public ResultDTO addSalaryBillInfo(SalaryBillPullDTO salaryBillPullDTO) {

        ResultDTO resultDTO = new ResultDTO();
        /*
         * 判断此工资条是否已存在
         * 若存在,进行更新
         * 如不存在,执行add方法
         */
        String is_exist = "select count(1) from salary_bill where month = '"+salaryBillPullDTO.getMonth()+"' and member_id = '"+
                salaryBillPullDTO.getMember_id()+"'";
        String exist = this.getReportingToOne(is_exist);
        if(!exist.equals("0")){
            return updateSalaryBillInfo(salaryBillPullDTO);
        }
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat();
        String time = simpleDateFormat.format(new Date());
        String uuid = UUID.randomUUID().toString().trim().replaceAll("-", "");

        String insert = "insert into salary_bill (salary_bill_id,month,member_id,member_name,base_wage,position_salary,merit_pay,school_age_wage,allowance," +
                "post_subsidy,meal_allowance,overtime_allowance,holiday_overtime_allowance,salary_add_total,compassionate_leave,sick_leave," +
                "join_or_quit,probation_period,endowment_insurance,housing_fund,salary_deduct_total,other_payroll_credit,salary_total," +
                "income_tax,actual_salary,remark,create_time,update_time,other_one,other_two,other_three,other_four,other_five) values ('"+ uuid+"','"+salaryBillPullDTO.getMonth()+"','"+
                salaryBillPullDTO.getMember_id()+"','"+salaryBillPullDTO.getMember_name()+"','"+salaryBillPullDTO.getBase_wage()+"','"+salaryBillPullDTO.getPosition_salary()+"','"+salaryBillPullDTO.getMerit_pay()+"','"+
                salaryBillPullDTO.getSchool_age_wage()+"','"+salaryBillPullDTO.getAllowance()+"','"+salaryBillPullDTO.getPost_subsidy()+"','"+salaryBillPullDTO.getMeal_allowance()+"','"+
                salaryBillPullDTO.getOvertime_allowance()+"','"+salaryBillPullDTO.getHoliday_overtime_allowance()+"','"+salaryBillPullDTO.getSalary_add_total()+"','"+salaryBillPullDTO.getCompassionate_leave()+"','"+
                salaryBillPullDTO.getSick_leave()+"','"+salaryBillPullDTO.getJoin_or_quit()+"','"+salaryBillPullDTO.getProbation_period()+"','"+salaryBillPullDTO.getEndowment_insurance()+"','"+
                salaryBillPullDTO.getHousing_fund()+"','"+salaryBillPullDTO.getSalary_deduct_total()+"','"+salaryBillPullDTO.getOther_payroll_credit()+"','"+salaryBillPullDTO.getSalary_total()+"','"+
                salaryBillPullDTO.getIncome_tax()+"','"+salaryBillPullDTO.getActual_salary()+"','"+salaryBillPullDTO.getRemark()+"','"+time+"','"+time+"','"
                + salaryBillPullDTO.getOther_one() + "','" + salaryBillPullDTO.getOther_two() + "','" + salaryBillPullDTO.getOther_three() + "','"
                + salaryBillPullDTO.getOther_four() + "','" + salaryBillPullDTO.getOther_five() + "')";
        int result = addReportingTo(insert);
//        System.out.println(insert);
        if(result!=0){
            resultDTO.setStr(uuid);
            resultDTO.setStatus("90000");
            resultDTO.setDescritpion(warnSrc.add_success);
        } else {
            resultDTO.setStatus("90001");
            resultDTO.setDescritpion(warnSrc.add_fail);
        }
        return resultDTO;
    }

    /**
     * 工资条信息批量添加
     *
     * @param salaryBillPullDTO
     */
    @Override
    public ResultDTO addSalaryBillListInfo(SalaryBillPullDTO salaryBillPullDTO) {

        ResultDTO resultDTO = new ResultDTO();
        List<String> resultList = new ArrayList<String>();
        try {
            JSONArray jsonArray = new JSONArray(salaryBillPullDTO.getSalary_bill_list());
            for(int i=0;i<jsonArray.length();i++){
                JSONObject object = new JSONObject();
                object = jsonArray.getJSONObject(i);
                SalaryBillPullDTO salaryBill = new SalaryBillPullDTO();

                salaryBill.setMonth(object.has("month")?object.getString("month"):null);
                salaryBill.setMember_id(object.has("member_id")?object.getString("member_id"):null);
                salaryBill.setMember_name(object.has("member_name")?object.getString("member_name"):null);
                salaryBill.setBase_wage(object.has("base_wage")?object.getString("base_wage"):null);
                salaryBill.setPosition_salary(object.has("position_salary")?object.getString("position_salary"):null);
                salaryBill.setMerit_pay(object.has("merit_pay")?object.getString("merit_pay"):null);
                salaryBill.setSchool_age_wage(object.has("school_age_wage")?object.getString("school_age_wage"):null);
                salaryBill.setAllowance(object.has("allowance")?object.getString("allowance"):null);
                salaryBill.setPost_subsidy(object.has("post_subsidy")?object.getString("post_subsidy"):null);
                salaryBill.setMeal_allowance(object.has("meal_allowance")?object.getString("meal_allowance"):null);
                salaryBill.setOvertime_allowance(object.has("overtime_allowance")?object.getString("overtime_allowance"):null);
                salaryBill.setHoliday_overtime_allowance(object.has("holiday_overtime_allowance")?object.getString("holiday_overtime_allowance"):null);
                salaryBill.setSalary_add_total(object.has("salary_add_total")?object.getString("salary_add_total"):null);
                salaryBill.setCompassionate_leave(object.has("compassionate_leave")?object.getString("compassionate_leave"):null);
                salaryBill.setSick_leave(object.has("sick_leave")?object.getString("sick_leave"):null);
                salaryBill.setJoin_or_quit(object.has("join_or_quit")?object.getString("join_or_quit"):null);
                salaryBill.setProbation_period(object.has("probation_period")?object.getString("probation_period"):null);
                salaryBill.setEndowment_insurance(object.has("endowment_insurance")?object.getString("endowment_insurance"):null);
                salaryBill.setHousing_fund(object.has("housing_fund")?object.getString("housing_fund"):null);
                salaryBill.setSalary_deduct_total(object.has("salary_deduct_total")?object.getString("salary_deduct_total"):null);
                salaryBill.setOther_payroll_credit(object.has("other_payroll_credit")?object.getString("other_payroll_credit"):null);
                salaryBill.setSalary_total(object.has("salary_total")?object.getString("salary_total"):null);
                salaryBill.setIncome_tax(object.has("income_tax")?object.getString("income_tax"):null);
                salaryBill.setActual_salary(object.has("actual_salary")?object.getString("actual_salary"):null);
                salaryBill.setRemark(object.has("remark")?object.getString("remark"):null);

                salaryBill.setOther_one(object.has("other_one")?object.getString("other_one"):null);
                salaryBill.setOther_two(object.has("other_two")?object.getString("other_two"):null);
                salaryBill.setOther_three(object.has("other_three")?object.getString("other_three"):null);
                salaryBill.setOther_four(object.has("other_four")?object.getString("other_four"):null);
                salaryBill.setOther_five(object.has("other_five")?object.getString("other_five"):null);

                ResultDTO result= addSalaryBillInfo(salaryBill);
                String uuid=result.getStr();
                if(result.getStatus().equals("90000")){
                    /**
                     * 事物提醒
                     */
                    SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    String time = df.format(new Date());
                    RemindPullDTO remindPullDTO = new RemindPullDTO();
                    remindPullDTO.setRemind_content("您有一条"+salaryBill.getMonth()+"月的工资单");
                    remindPullDTO.setRemind_time(time);
                    remindPullDTO.setRemind_type("22");
                    remindPullDTO.setRemind_form_id(uuid);
                    remindPullDTO.setRemind_order("0");
                    remindPullDTO.setCreate_time(time);
                    remindPullDTO.setRemind_endtime(salaryBill.getUpdate_time());
                    remindPullDTO.setRemind_member_id(salaryBill.getMember_id());
                    remindPullDTO.setRemind_starttime(salaryBill.getCreate_time());
                    remindPullDTO.setRemind_descritpion("");
                    remindResource.addRemindInfo(remindPullDTO);
                }
                resultList.add(result.getStr());
            }
            if (resultList.size() != 0) {
                resultDTO.setStatus("90000");
                resultDTO.setDescritpion(warnSrc.add_success);
            } else {
                resultDTO.setStatus("90001");
                resultDTO.setDescritpion(warnSrc.add_fail);
            }
            resultDTO.setStr(resultList.size() + "");
        } catch (JSONException e) {
            e.printStackTrace();
        }
        return resultDTO;
    }

    /**
     * 工资条信息修改
     *
     * @param salaryBillPullDTO
     */
    @Override
    public ResultDTO updateSalaryBillInfo(SalaryBillPullDTO salaryBillPullDTO) {

        ResultDTO resultDTO = new ResultDTO();

        //更新sql语句
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String time = df.format(new Date());
        String update = "update salary_bill set update_time='"+time+"'";
        if(salaryBillPullDTO.getMonth()!=null&&!"".equals(salaryBillPullDTO.getMonth())){
            update += ",month = '"+salaryBillPullDTO.getMonth()+"'";
        }
        if(salaryBillPullDTO.getMember_id()!=null&&!"".equals(salaryBillPullDTO.getMember_id())){
            update += ",member_id = '"+salaryBillPullDTO.getMember_id()+"'";
        }
        if(salaryBillPullDTO.getMember_name()!=null&&!"".equals(salaryBillPullDTO.getMember_name())){
            update += ",member_name = '"+salaryBillPullDTO.getMember_name()+"'";
        }
        if(salaryBillPullDTO.getBase_wage()!=null&&!"".equals(salaryBillPullDTO.getBase_wage())){
            update += ",base_wage = '"+salaryBillPullDTO.getBase_wage()+"'";
        }
        if(salaryBillPullDTO.getPosition_salary()!=null&&!"".equals(salaryBillPullDTO.getPosition_salary())){
            update += ",position_salary = '"+salaryBillPullDTO.getPosition_salary()+"'";
        }
        if(salaryBillPullDTO.getMerit_pay()!=null&&!"".equals(salaryBillPullDTO.getMerit_pay())){
            update += ",merit_pay = '"+salaryBillPullDTO.getMerit_pay()+"'";
        }
        if(salaryBillPullDTO.getSchool_age_wage()!=null&&!"".equals(salaryBillPullDTO.getSchool_age_wage())){
            update += ",school_age_wage = '"+salaryBillPullDTO.getSchool_age_wage()+"'";
        }
        if(salaryBillPullDTO.getAllowance()!=null&&!"".equals(salaryBillPullDTO.getAllowance())){
            update += ",allowance = '"+salaryBillPullDTO.getAllowance()+"'";
        }
        if(salaryBillPullDTO.getPost_subsidy()!=null&&!"".equals(salaryBillPullDTO.getPost_subsidy())){
            update += ",post_subsidy = '"+salaryBillPullDTO.getPost_subsidy()+"'";
        }
        if(salaryBillPullDTO.getMeal_allowance()!=null&&!"".equals(salaryBillPullDTO.getMeal_allowance())){
            update += ",meal_allowance = '"+salaryBillPullDTO.getMeal_allowance()+"'";
        }
        if(salaryBillPullDTO.getOvertime_allowance()!=null&&!"".equals(salaryBillPullDTO.getOvertime_allowance())){
            update += ",overtime_allowance = '"+salaryBillPullDTO.getOvertime_allowance()+"'";
        }
        if(salaryBillPullDTO.getHoliday_overtime_allowance()!=null&&!"".equals(salaryBillPullDTO.getHoliday_overtime_allowance())){
            update += ",holiday_overtime_allowance = '"+salaryBillPullDTO.getHoliday_overtime_allowance()+"'";
        }
        if(salaryBillPullDTO.getSalary_add_total()!=null&&!"".equals(salaryBillPullDTO.getSalary_add_total())){
            update += ",salary_add_total = '"+salaryBillPullDTO.getSalary_add_total()+"'";
        }
        if(salaryBillPullDTO.getCompassionate_leave()!=null&&!"".equals(salaryBillPullDTO.getCompassionate_leave())){
            update += ",compassionate_leave = '"+salaryBillPullDTO.getCompassionate_leave()+"'";
        }
        if(salaryBillPullDTO.getSick_leave()!=null&&!"".equals(salaryBillPullDTO.getSick_leave())){
            update += ",sick_leave = '"+salaryBillPullDTO.getSick_leave()+"'";
        }
        if(salaryBillPullDTO.getJoin_or_quit()!=null&&!"".equals(salaryBillPullDTO.getJoin_or_quit())){
            update += ",join_or_quit = '"+salaryBillPullDTO.getJoin_or_quit()+"'";
        }
        if(salaryBillPullDTO.getProbation_period()!=null&&!"".equals(salaryBillPullDTO.getProbation_period())){
            update += ",probation_period = '"+salaryBillPullDTO.getProbation_period()+"'";
        }
        if(salaryBillPullDTO.getEndowment_insurance()!=null&&!"".equals(salaryBillPullDTO.getEndowment_insurance())){
            update += ",endowment_insurance = '"+salaryBillPullDTO.getEndowment_insurance()+"'";
        }
        if(salaryBillPullDTO.getHousing_fund()!=null&&!"".equals(salaryBillPullDTO.getHousing_fund())){
            update += ",housing_fund = '"+salaryBillPullDTO.getHousing_fund()+"'";
        }
        if(salaryBillPullDTO.getSalary_deduct_total()!=null&&!"".equals(salaryBillPullDTO.getSalary_deduct_total())){
            update += ",salary_deduct_total = '"+salaryBillPullDTO.getSalary_deduct_total()+"'";
        }
        if(salaryBillPullDTO.getOther_payroll_credit()!=null&&!"".equals(salaryBillPullDTO.getOther_payroll_credit())){
            update += ",other_payroll_credit = '"+salaryBillPullDTO.getOther_payroll_credit()+"'";
        }
        if(salaryBillPullDTO.getSalary_total()!=null&&!"".equals(salaryBillPullDTO.getSalary_total())){
            update += ",salary_total = '"+salaryBillPullDTO.getSalary_total()+"'";
        }
        if(salaryBillPullDTO.getIncome_tax()!=null&&!"".equals(salaryBillPullDTO.getIncome_tax())){
            update += ",income_tax = '"+salaryBillPullDTO.getIncome_tax()+"'";
        }
        if(salaryBillPullDTO.getActual_salary()!=null&&!"".equals(salaryBillPullDTO.getActual_salary())){
            update += ",actual_salary = '"+salaryBillPullDTO.getActual_salary()+"'";
        }
        if(salaryBillPullDTO.getRemark()!=null&&!"".equals(salaryBillPullDTO.getRemark())){
            update += ",remark = '"+salaryBillPullDTO.getRemark()+"'";
        }

        if(salaryBillPullDTO.getOther_one()!=null&&!"".equals(salaryBillPullDTO.getOther_one())){
            update += ",other_one = '"+salaryBillPullDTO.getOther_one()+"'";
        }

        if(salaryBillPullDTO.getOther_two()!=null&&!"".equals(salaryBillPullDTO.getOther_two())){
            update += ",other_two = '"+salaryBillPullDTO.getOther_two()+"'";
        }

        if(salaryBillPullDTO.getOther_three()!=null&&!"".equals(salaryBillPullDTO.getOther_three())){
            update += ",other_three = '"+salaryBillPullDTO.getOther_three()+"'";
        }

        if(salaryBillPullDTO.getOther_four()!=null&&!"".equals(salaryBillPullDTO.getOther_four())){
            update += ",other_four = '"+salaryBillPullDTO.getOther_four()+"'";
        }

        if(salaryBillPullDTO.getOther_five()!=null&&!"".equals(salaryBillPullDTO.getOther_five())){
            update += ",other_five = '"+salaryBillPullDTO.getOther_five()+"'";
        }


        update += " where salary_bill_id = '"+salaryBillPullDTO.getSalary_bill_id()+"'";
        int result = addReportingTo(update);
        System.out.println(update);
        // 判断SQl是否执行成功
        if(result!=0){
            resultDTO.setStr(salaryBillPullDTO.getSalary_bill_id());
            resultDTO.setStatus("90000");
            resultDTO.setDescritpion(warnSrc.update_success);
        } else {
            resultDTO.setStatus("90001");
            resultDTO.setDescritpion(warnSrc.update_fail);
        }
        return resultDTO;
    }

    /**
     * 工资条信息删除
     *
     * @param salaryBillPullDTO
     */
    @Override
    public ResultDTO deleteSalaryBillInfo(SalaryBillPullDTO salaryBillPullDTO) {

        ResultDTO resultDTO = new ResultDTO();

        //判断是否存在
        String sql = "select count(1) from salary_bill where salary_bill_id = '"+salaryBillPullDTO.getSalary_bill_id()+"'";
        String exist = getReportingToOne(sql);

        int result = 0;
        if(!exist.equals("0")){
            String delete = "delete from salary_bill where salary_bill_id = '"+salaryBillPullDTO.getSalary_bill_id()+"'";
            result = addReportingTo(delete);
            System.out.println(delete);
        }
        // 判断SQl是否执行成功
        if (result != 0) {
            resultDTO.setStatus("90000");
            resultDTO.setDescritpion(warnSrc.delete_success);
        } else {
            resultDTO.setStatus("90001");
            resultDTO.setDescritpion(warnSrc.delete_fail);
        }
        return resultDTO;
    }

    /**
     * 导出Excel表格
     */
    @Override
    public ResultDTO downloadExcel(SalaryBillPullDTO salaryBillPullDTO){

        ResultDTO resultDTO = new ResultDTO();

        // 第一步，创建一个webbook，对应一个Excel文件
        XSSFWorkbook wb = new XSSFWorkbook();
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = wb.createSheet("工资条项目表");
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        XSSFRow row0 = sheet.createRow((int) 0);
        XSSFRow row1 = sheet.createRow((int) 1);
        //设置表头样式
        XSSFFont font = wb.createFont();
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        XSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        style.setFont(font);
        //单元格内自动换行
        style.setWrapText(true);
        //隐藏id列
        sheet.setColumnWidth(1,0);

        XSSFCell cell = row0.createCell(0);
        cell.setCellValue("月份");
        cell.setCellStyle(style);
        cell = row0.createCell(1);
        cell.setCellValue("id");
        cell.setCellStyle(style);
        cell = row0.createCell(2);
        cell.setCellValue("姓名");
        cell.setCellStyle(style);
        cell = row0.createCell(3);
        cell.setCellValue("应发工资项目");
        cell.setCellStyle(style);
        cell = row0.createCell(13);
        cell.setCellValue("应扣工资项目");
        cell.setCellStyle(style);
        cell = row0.createCell(20);
        cell.setCellValue("其他代发工资");
        cell.setCellStyle(style);
        cell = row0.createCell(26);
        cell.setCellValue("本月工资");
        cell.setCellStyle(style);
        cell = row0.createCell(27);
        cell.setCellValue("代扣个人所得税");
        cell.setCellStyle(style);
        cell = row0.createCell(28);
        cell.setCellValue("本月实发(注意保密)");
        cell.setCellStyle(style);

        cell = row1.createCell(3);
        cell.setCellValue("基本工资");
        cell.setCellStyle(style);
        cell = row1.createCell(4);
        cell.setCellValue("岗位工资");
        cell.setCellStyle(style);
        cell = row1.createCell(5);
        cell.setCellValue("绩效工资");
        cell.setCellStyle(style);
        cell = row1.createCell(6);
        cell.setCellValue("校龄工资");
        cell.setCellStyle(style);
        cell = row1.createCell(7);
        cell.setCellValue("津贴");
        cell.setCellStyle(style);
        cell = row1.createCell(8);
        cell.setCellValue("岗位补贴");
        cell.setCellStyle(style);
        cell = row1.createCell(9);
        cell.setCellValue("餐补");
        cell.setCellStyle(style);
        cell = row1.createCell(10);
        cell.setCellValue("行政值班平时加班");
        cell.setCellStyle(style);
        cell = row1.createCell(11);
        cell.setCellValue("周末及法定假日加值班");
        cell.setCellStyle(style);
        cell = row1.createCell(12);
        cell.setCellValue("应发小计");
        cell.setCellStyle(style);
        cell = row1.createCell(13);
        cell.setCellValue("事假");
        cell.setCellStyle(style);
        cell = row1.createCell(14);
        cell.setCellValue("病假");
        cell.setCellStyle(style);
        cell = row1.createCell(15);
        cell.setCellValue("新进离职及其他扣款");
        cell.setCellStyle(style);
        cell = row1.createCell(16);
        cell.setCellValue("试用期扣款");
        cell.setCellStyle(style);
        cell = row1.createCell(17);
        cell.setCellValue("养老保险金");
        cell.setCellStyle(style);
        cell = row1.createCell(18);
        cell.setCellValue("住房公积金");
        cell.setCellStyle(style);
        cell = row1.createCell(19);
        cell.setCellValue("应扣小计");
        cell.setCellStyle(style);

        cell = row1.createCell(20);
        cell.setCellValue("其它代发工资");
        cell.setCellStyle(style);

        cell = row1.createCell(21);
        cell.setCellValue("其它1");
        cell.setCellStyle(style);

        cell = row1.createCell(22);
        cell.setCellValue("其它2");
        cell.setCellStyle(style);

        cell = row1.createCell(23);
        cell.setCellValue("其它3");
        cell.setCellStyle(style);

        cell = row1.createCell(24);
        cell.setCellValue("其它4");
        cell.setCellStyle(style);

        cell = row1.createCell(25);
        cell.setCellValue("其它5");
        cell.setCellStyle(style);

        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0,0,3,12));
        sheet.addMergedRegion(new CellRangeAddress(0,0,13,19));
        sheet.addMergedRegion(new CellRangeAddress(0,1,0,0));
        sheet.addMergedRegion(new CellRangeAddress(0,1,1,1));
        sheet.addMergedRegion(new CellRangeAddress(0,1,2,2));
        sheet.addMergedRegion(new CellRangeAddress(0,0,20,25));
//        sheet.addMergedRegion(new CellRangeAddress(0,1,21,25));
        sheet.addMergedRegion(new CellRangeAddress(0,1,26,26));
        sheet.addMergedRegion(new CellRangeAddress(0,1,27,27));
        sheet.addMergedRegion(new CellRangeAddress(0,1,28,28));

        // 第五步，写入实体数据 实际应用中这些数据从数据库得到，
        try {
            JSONArray array = new JSONArray(salaryBillPullDTO.getMember_list());
            for (int i=0;i<array.length();i++){
                XSSFRow row = sheet.createRow(i+2);
                row.createCell(0).setCellValue(Calendar.getInstance().get(Calendar.MONTH)+1);
                row.createCell(1).setCellValue(array.getJSONObject(i).getString("member_id"));
                row.createCell(2).setCellValue(array.getJSONObject(i).getString("member_name"));
            }
        } catch (JSONException e) {
            e.printStackTrace();
        }

        //下载excel
        Response response = null;
        String addr = "";
        try {
            String[] file_path = this.getImage_path();
            String file_pathAddr = file_path[1];//服务器项目目录地址
            String file_pathSerAddr = file_path[2];//文件访问地址
            //缓存文件到服务器本地
            String fileDirct = file_pathAddr + "/SalaryBill/download";
            File f = new File(fileDirct);
            if (!f.exists()) {
                f.mkdirs();
                f.setWritable(true, false);
            }
            String fileName = Calendar.getInstance().get(Calendar.YEAR)+"年"+(Calendar.getInstance().get(Calendar.MONTH)+1)+"月人员工资表.xlsx";// 文件名
            FileOutputStream fout = new FileOutputStream(fileDirct+"/"+fileName);
            wb.write(fout);
            fout.close();
            File file = new File(fileDirct+"/"+fileName);
            long fileLength = file.length();
            Response.ResponseBuilder responseBuilder = Response.ok(file);
            responseBuilder.type("application/x-msdownload");
            responseBuilder.header("Content-Disposition", "attachment; filename="+URLEncoder.encode(fileName, "UTF-8"));
            responseBuilder.header("Content-Length", Long.toString(fileLength));
            response = responseBuilder.build();
            addr = file_pathSerAddr+"/SalaryBill/download/"+fileName;
        } catch (Exception e) {
            e.printStackTrace();
        }
        resultDTO.setStr(addr);
        resultDTO.setStatus("90000");
        resultDTO.setDescritpion("下载成功");
        return resultDTO;
    }

    /**
     * 解析导入的Excel表格
     *
     * @param attachmentPullDTO
     */
    @Override
    public ResultDTO poiExcel(AttachmentPullDTO attachmentPullDTO) {

        ResultDTO resultDTO = new ResultDTO();

        String[] file_path = this.getImage_path();
        String file_pathAddr = file_path[1];//服务器项目目录地址
        //获取配置地址
        String fileDirct = file_pathAddr + "/SalaryBill/upload";
        File f = new File(fileDirct);
        if (!f.exists()) {
            f.mkdirs();
            f.setWritable(true, false);
        }
        String filePath = fileDirct + "/" + attachmentPullDTO.getFile_name();
        //base64字符串保存为文件
        boolean headImg = FileProcess.GenerateImage(attachmentPullDTO.getFile_stream(), filePath);
        FileProcess.newlog("执行文件储存方法，储存文件地址：" + filePath + " 方法返回：" + headImg);

        if(headImg){
            List<SalaryBillShowDTO> result = new ArrayList<SalaryBillShowDTO>();
            PoiExcelHelper helper = new PoiExcel2k7Helper();
            // 读取excel文件数据
            ArrayList<ArrayList<String>> dataList = helper.readExcel(filePath,0,"3-");
            for (int i=0;i<dataList.size();i++){
                ArrayList<String> data = dataList.get(i);
                SalaryBillShowDTO salaryBillShowDTO = new SalaryBillShowDTO();
                salaryBillShowDTO.setMonth(data.get(0)  == null ? "" : data.get(0));
                salaryBillShowDTO.setMember_id(data.get(1)  == null ? "" : data.get(1));
                salaryBillShowDTO.setMember_name(data.get(2)  == null ? "" : data.get(2));
                salaryBillShowDTO.setBase_wage(data.get(3)  == null ? "" : data.get(3));
                salaryBillShowDTO.setPosition_salary(data.get(4)  == null ? "" : data.get(4));
                salaryBillShowDTO.setMerit_pay(data.get(5)  == null ? "" : data.get(5));
                salaryBillShowDTO.setSchool_age_wage(data.get(6)  == null ? "" : data.get(6));
                salaryBillShowDTO.setAllowance(data.get(7)  == null ? "" : data.get(7));
                salaryBillShowDTO.setPost_subsidy(data.get(8)  == null ? "" : data.get(8));
                salaryBillShowDTO.setMeal_allowance(data.get(9)  == null ? "" : data.get(9));
                salaryBillShowDTO.setOvertime_allowance(data.get(10)  == null ? "" : data.get(10));
                salaryBillShowDTO.setHoliday_overtime_allowance(data.get(11)  == null ? "" : data.get(11));
                salaryBillShowDTO.setSalary_add_total(data.get(12)  == null ? "" : data.get(12));
                salaryBillShowDTO.setCompassionate_leave(data.get(13)  == null ? "" : data.get(13));
                salaryBillShowDTO.setSick_leave(data.get(14)  == null ? "" : data.get(14));
                salaryBillShowDTO.setJoin_or_quit(data.get(15)  == null ? "" : data.get(15));
                salaryBillShowDTO.setProbation_period(data.get(16)  == null ? "" : data.get(16));
                salaryBillShowDTO.setEndowment_insurance(data.get(17)  == null ? "" : data.get(17));
                salaryBillShowDTO.setHousing_fund(data.get(18)  == null ? "" : data.get(18));
                salaryBillShowDTO.setSalary_deduct_total(data.get(19)  == null ? "" : data.get(19));
                salaryBillShowDTO.setOther_payroll_credit(data.get(20)  == null ? "" : data.get(20));


                salaryBillShowDTO.setOther_one(data.get(21) == null ? "" : data.get(21));
                salaryBillShowDTO.setOther_two(data.get(22) == null ? "" : data.get(22));
                salaryBillShowDTO.setOther_three(data.get(23) == null ? "" : data.get(23));
                salaryBillShowDTO.setOther_four(data.get(24) == null ? "" : data.get(24));
                salaryBillShowDTO.setOther_five(data.get(25) == null ? "" : data.get(25));

                salaryBillShowDTO.setSalary_total(data.get(26)  == null ? "" : data.get(26));
                salaryBillShowDTO.setIncome_tax(data.get(27)  == null ? "" : data.get(27));
                salaryBillShowDTO.setActual_salary(data.get(28)  == null ? "" : data.get(28));


                result.add(salaryBillShowDTO);
            }
            if (result.size() != 0) {
                resultDTO.setStatus("90000");
                resultDTO.setDescritpion(warnSrc.get_success);
            } else {
                resultDTO.setStatus("90001");
                resultDTO.setDescritpion(warnSrc.get_fail);
            }
            resultDTO.setStr(result.size() + "");
            resultDTO.setValues(result);
        }
        return resultDTO;
    }

    /**
     * 解析导入的自定义Excel工资表
     *
     * @param attachmentPullDTO
     */
    @Override
    public ResultDTO poiSalaryExcel(AttachmentPullDTO attachmentPullDTO) {

        ResultDTO resultDTO = new ResultDTO();

        String[] file_path = this.getImage_path();
        String file_pathAddr = file_path[1];//服务器项目目录地址
        //获取配置地址
        String fileDirct = file_pathAddr + "/SalaryBill/upload";
        File f = new File(fileDirct);
        if (!f.exists()) {
            f.mkdirs();
            f.setWritable(true, false);
        }
        String filePath = fileDirct + "/" + attachmentPullDTO.getFile_name();
        //base64字符串保存为文件
        boolean headImg = FileProcess.GenerateImage(attachmentPullDTO.getFile_stream(), filePath);
        FileProcess.newlog("执行文件储存方法，储存文件地址：" + filePath + " 方法返回：" + headImg);

        if(headImg){
            PoiExcelHelper helper = null;
            if(filePath.contains(".xls")){
                helper = new PoiExcel2k3Helper();
            }
            if(filePath.contains(".xlsx")){
                helper = new PoiExcel2k7Helper();
            }
            // 读取excel文件数据
            ArrayList<ArrayList<String>> dataList = helper.readExcel(filePath,0,"1-");
            ArrayList<ArrayList<String>> result = new ArrayList<ArrayList<String>>();
            for(int i=0;i<dataList.size();i++){
                if(null!=dataList.get(i).get(0)&&!"".equals(dataList.get(i).get(0).trim())){
                    result.add(dataList.get(i));
                }
            }
            if(result.size()>1){
                resultDTO.setStr(result.size()+"");
                resultDTO.setStatus("90000");
                resultDTO.setDescritpion("Excel解析成功");
                resultDTO.setValues(result);
            }else {
                resultDTO.setStatus("90001");
                resultDTO.setDescritpion("Excel数据为空");
            }
        }else {
            resultDTO.setStatus("90001");
            resultDTO.setDescritpion("Excel解析失败");
        }
        return resultDTO;
    }

    /**
     * 导入工资信息
     *
     * @param salaryInfoPullDTO
     */
    @Override
    public ResultDTO importSalaryInfo(SalaryInfoPullDTO salaryInfoPullDTO) {

        ResultDTO resultDTO = new ResultDTO();

        String salary_type = "";

        if("1".equals(salaryInfoPullDTO.getSalary_type())){
            salary_type = "guofa";
        }else if ("2".equals(salaryInfoPullDTO.getSalary_type())){
            salary_type = "xiaofa";
        }

        String tableName = salaryInfoPullDTO.getYear()+"_"+salaryInfoPullDTO.getMonth()+"_"+salary_type;

//        ImportExcelTable importExcelTable = new ImportExcelTable();

        int result = ImportExcel(salaryInfoPullDTO.getSalary_info(),tableName);

        if(result==1){
            resultDTO.setStatus("90000");
            resultDTO.setDescritpion(warnSrc.add_success);
        }else{
            resultDTO.setStatus("90001");
            resultDTO.setDescritpion(warnSrc.add_fail);
        }
        return resultDTO;
    }

    /**
     * 查询工资信息
     *
     * @param search
     */
    @Override
    public ResultDTO getSalaryInfo(String search) {

        ResultDTO resultDTO = new ResultDTO();

        try {
            JSONObject json = new JSONObject(search);

            String sql_w = "";
            if(json.has("member_id")){
                if(null!=json.getString("member_id")&&!"".equals(json.getString("member_id"))){
                    sql_w += " AND km.member_id = '" +json.getString("member_id")+"'";
                }
            }

            String year = json.has("year")?json.getString("year"):null;
            String month = json.has("month")?json.getString("month"):null;
            String salary_type = "";
            if("1".equals(json.getString("salary_type"))){
                salary_type = "guofa";
            }else if ("2".equals(json.getString("salary_type"))){
                salary_type = "xiaofa";
            }

            String tableName = year+"_"+month+"_"+salary_type;

            String exist = "SELECT COUNT(1) FROM information_schema.TABLES WHERE table_name = '" + tableName + "'";

            String existTable = getReportingToOne(exist);

            if(!"0".equals(existTable)){
                ArrayList<ArrayList<String>> result = new ArrayList<>();
                String sql_col = "SELECT COLUMN_NAME FROM information_schema.columns WHERE table_name= '" + tableName + "'";
                ArrayList<String> colName = new ArrayList<String>();
                List<String[]> col = getReportingTo(sql_col);
                for(int i=0;i<col.size();i++){
                    colName.add(col.get(i)[1]);
                }
                result.add(colName);
                String sql_info = "SELECT DISTINCT "+salary_type+".* FROM kam_member km LEFT JOIN "+tableName+" "+salary_type+
                        " ON "+salary_type+".银行账号 = km.bank_account WHERE 1=1 " + sql_w;
                ArrayList<ArrayList<String>> salaryList = new ArrayList<ArrayList<String>>();
                List<String[]> info = getReportingTo(sql_info);
                for(int i=0;i<info.size();i++){
                    ArrayList<String> salary = new ArrayList<String>();
                    for(int j=1;j<info.get(i).length;j++){
                        salary.add(info.get(i)[j]);
                    }
                    salaryList.add(salary);
                    result.add(salary);
                }
                if(result.size()!=0){
                    resultDTO.setStr(salaryList.size()+"");
                    resultDTO.setStatus("90000");
                    resultDTO.setDescritpion(warnSrc.get_success);
                    resultDTO.setValues(result);
                }else {
                    resultDTO.setStatus("90001");
                    resultDTO.setDescritpion(warnSrc.get_fail);
                }
            }else {
                resultDTO.setStatus("90001");
                resultDTO.setDescritpion(warnSrc.get_fail);
            }
        } catch (JSONException e) {
            e.printStackTrace();
        }
        return resultDTO;
    }


    private int ImportExcel(ArrayList<ArrayList<String>> dataList, String tableName){

        String insertSqlColum = "";

        //创建数据表
        String sql ="create table " + tableName + " (";

        ArrayList<String> data = dataList.get(0);

        for(int i=0;i<data.size();i++){

            if(i < data.size() -1){
                sql += "`" + data.get(i).toString() + "`" + " VARCHAR(100), ";
                insertSqlColum += "`" + data.get(i).toString() + "`," ;
            }

            if(i == data.size() - 1){
                sql += "`" + data.get(i).toString() + "`" + " VARCHAR(100)";
                insertSqlColum += "`" + data.get(i).toString() + "`" ;
            }
        }

        sql += ")";

        String existTable = "SELECT count(1) FROM information_schema.TABLES WHERE table_name ='"+ tableName + "';";
        int Creatresult = Integer.parseInt(this.getReportingToOne(existTable));

        if(Creatresult < 1){
            this.addReportingTo(sql);  //创建数据表
        }

        if(Creatresult > 0){

            String dropTable = "DROP TABLE "+tableName;

            this.addReportingTo(dropTable);//删除数据表
            this.addReportingTo(sql);  //创建数据表

            for(int i =1;i<dataList.size();i++){
                ArrayList<String> datalist = dataList.get(i);
                //excel插入语句
                String insertSql = " insert into " + tableName + "(" + insertSqlColum + ") values (";
                for(int j=0;j<data.size();j++){

                    if(j < data.size() - 1 ){
                        insertSql += "'" + datalist.get(j).toString() + "',";
                    }

                    if(j == data.size() -1){
                        insertSql += "'" + datalist.get(j).toString() + "'";
                    }

                }

                insertSql += ")";

                int result = this.addReportingTo(insertSql);
            }
        }

        return Creatresult;

    }
}
